/**
 * 
 */
package org.gov.vis.util;

/**
 * @author Vishwanath
 *
 */
public class DBStatements {

	//Statements for Department
	public static final String SELECT_DEPARTMENTS = 
		"SELECT dpt_id, dpt_code, dpt_inst, dpt_vlg, dpt_mdl, dpt_dst " +
		"FROM department " +
		"WHERE dpt_id BETWEEN ? AND ? " +
		"AND dpt_code LIKE ?";

	public static final String ADD_DEPARTMENT =
		"INSERT INTO department (dpt_code, dpt_inst, dpt_vlg, dpt_mdl, dpt_dst) " +
		"VALUES (?, ?, ?, ?, ?)";

	public static final String EDIT_DEPARTMENT =
		"UPDATE department " +
		"SET dpt_code = ?, " +
		"dpt_inst = ?, " +
		"dpt_vlg = ?, " +
		"dpt_mdl = ?, " +
		"dpt_dst = ? " +
		"WHERE dpt_id = ?";

	public static final String DELETE_DEPARTMENT =
		"DELETE FROM department " +
		"WHERE dpt_id = ?";

	//Statements for GopalaMithra 
	public static final String SELECT_GOPALAMITHRAS =
		"SELECT gpm_id, gpm_code, gpm_name, gpm_first_name, gpm_dob, gpm_ntv_vlg, gpm_mdl, gpm_dst, gpm_jrsdtn, gpm_hq, gpm_hqmdl, gpm_hqdst " +
		"FROM gopalamithra " +
		"WHERE gpm_id BETWEEN ? AND ? " +
		"AND gpm_code LIKE ? " +
		"AND gpm_name LIKE ? ";

	public static final String ADD_GOPALAMITHRA =
		"INSERT INTO gopalamithra (gpm_code, gpm_name, gpm_first_name, gpm_dob, gpm_ntv_vlg, gpm_mdl, gpm_dst, gpm_jrsdtn, gpm_hq, gpm_hqmdl, gpm_hqdst) " +
		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_GOPALAMITHRA =
		"UPDATE gopalamithra " +
		"SET gpm_code = ?, " +
		"gpm_name = ?, " +
		"gpm_first_name = ?, " +
		"gpm_dob =?, " +
		"gpm_ntv_vlg= ?, " +
		"gpm_mdl = ?, " +
		"gpm_dst = ?, " +
		"gpm_jrsdtn = ?, " +
		"gpm_hq = ?, " +
		"gpm_hqmdl = ?, " +
		"gpm_hqdst = ? " +
		"WHERE gpm_id = ?";

	public static final String DELETE_GOPALAMITHRA =
		"DELETE FROM gopalamithra " +
		"WHERE gpm_id = ?";

	//Statements for Others
	public static final String SELECT_OTHERS = 
		"SELECT oth_id, oth_code, oth_org, oth_name_aiworker, oth_hq, oth_jrsdtn, oth_mdl, oth_dst " +
		"FROM other " +
		"WHERE oth_id BETWEEN ? AND ? " +
		"AND oth_code LIKE ?";

	public static final String ADD_OTHER = 
		"INSERT INTO other (oth_code, oth_org, oth_name_aiworker, oth_hq, oth_jrsdtn, oth_mdl, oth_dst) " +
		"VALUES (?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_OTHER = 
		"UPDATE other " +
		"SET oth_code =?, " +
		"oth_org = ?, " +
		"oth_name_aiworker = ?, " +
		"oth_hq = ?, " +
		"oth_jrsdtn = ?, " +
		"oth_mdl = ?, " +
		"oth_dst = ? " +
		"WHERE oth_id = ?";

	public static final String DELETE_OTHER = 
		"DELETE FROM other " +
		"WHERE oth_id = ?";

	//Statements for Staff
	public static final String SELECT_STAFF =
		"SELECT stf_id, stf_code, stf_name, stf_father_name, stf_dob, stf_qualf, stf_dsg, stf_job_type, stf_doj, stf_vlg, stf_mdl, stf_dst " +
		"FROM staff  " +
		"WHERE stf_id BETWEEN ? AND ? " +
		"AND stf_code LIKE ? " +
		"AND stf_name LIKE ? " +
		"AND stf_job_type BETWEEN ? AND ?";

	public static final String ADD_STAFF = 
		"INSERT INTO staff (stf_code, stf_name, stf_father_name, stf_dob, stf_qualf, stf_dsg, stf_job_type, stf_doj, stf_vlg, stf_mdl, stf_dst) " +
		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_STAFF = 
		"UPDATE staff " +
		"SET stf_code = ?, " +
		"stf_name = ?, " +
		"stf_father_name = ?, " +
		"stf_dob = ?, " +
		"stf_qualf= ?, " +
		"stf_dsg = ?, " +
		"stf_job_type = ?, " +
		"stf_doj = ?, " +
		"stf_vlg = ?, " +
		"stf_mdl = ?, " +
		"stf_dst = ? " +
		"WHERE stf_id = ?";

	public static final String DELETE_STAFF = 
		"DELETE FROM staff " +
		"WHERE stf_id = ?";

	//Statements for AI

	public static final String SELECT_AI =
		"SELECT ai_id, ai_code, ai_doi, ai_toi, ai_breed, ai_bullno, ai_noai, ai_fee, ai_receipt, " +
		"ai_cattle_owner, ai_vlg, ai_mdl, ai_anm_typ, ai_anm_knd, ai_brth_gvn " +
		"FROM ai " +
		"WHERE ai_id BETWEEN ? AND ? " +
		"AND ai_code LIKE ? " +
		"AND ai_breed LIKE ? " +
		"AND ai_bullno LIKE ?";

	public static final String ADD_AI =
		"INSERT INTO ai (ai_code, ai_doi, ai_toi, ai_breed, ai_bullno, ai_noai, ai_fee, ai_receipt, " +
		"ai_cattle_owner, ai_vlg, ai_mdl, ai_anm_typ, ai_anm_knd, ai_brth_gvn) " +
		"VALUES (? , ? , ? , ? , ? , " +
		"? , ? , ? , ? , ? , " +
		"? , ? , ? , ?) ";

	public static final String EDIT_AI = 
		"UPDATE ai SET " +
		"ai_code = ?, " +
		"ai_doi = ?, " +
		"ai_toi = ?, " +
		"ai_breed = ?, " +
		"ai_bullno = ?, " +
		"ai_noai = ?, " +
		"ai_fee = ?, " +
		"ai_receipt = ?, " +
		"ai_cattle_owner = ?, " +
		"ai_vlg = ?, " +
		"ai_mdl = ?, " +
		"ai_anm_typ = ?, " +
		"ai_anm_knd = ?, " +
		"ai_brth_gvn = ? " +
		"WHERE ai_id = ?";

	public static final String DELETE_AI = 
		"DELETE FROM ai " +
		"WHERE ai_id = ?";

	//Statements for PROGENCY

	public static final String SELECT_PROGENCY = 
		"SELECT prgy_id, prgy_code, prgy_frmr_name,prgy_frmr_code, prgy_rltnshp_name, prgy_vlg, prgy_mdl, prgy_dst, " +
		"prgy_calf_dob, prgy_breed_type, prgy_prcnt, prgy_sex,prgy_color, prgy_tagno, prgy_breed, prgy_calving_num, prgy_ai_num, " +
		"prgy_prv_yield, prgy_prst_yield, prgy_bullno, " +
		"prgy_dm_milk, prgy_sire_breed " +
		"FROM progency " +
		"WHERE prgy_id BETWEEN ? AND ? " +
		"AND prgy_code LIKE ? " +
		"AND prgy_frmr_code LIKE ? " +
		"AND prgy_frmr_name LIKE ? " +
		"AND prgy_breed_type LIKE ? " +
		"AND prgy_bullno LIKE ?";	
	
	public static final String SELECT_MAX_PROGENCYID = 
		"SELECT MAX(prgy_id) " +
		"FROM progency";	

	public static final String ADD_PROGENCY = 
		"INSERT INTO progency (prgy_code,prgy_frmr_code, prgy_frmr_name, prgy_rltnshp_name, prgy_vlg, prgy_mdl, prgy_dst, " +
		"prgy_calf_dob, prgy_breed_type, prgy_prcnt, prgy_sex, prgy_color, prgy_tagno, prgy_breed, prgy_calving_num, prgy_ai_num," +
		"prgy_prv_yield, prgy_prst_yield, prgy_bullno, prgy_dm_milk,prgy_sire_breed ) " +
		"VALUES (? , ? , ? , ? , ? , " +
		"? , ? , ? , ? , ? , " +
		"? , ? , ? , ? , ? , " +
		"? , ? , ? , ? , ? , ?)";

	public static final String EDIT_PROGENCY = 
		"UPDATE progency SET " +
		"prgy_code = ?, " +
		"prgy_frmr_code = ?, " +
		"prgy_frmr_name = ?, " +
		"prgy_rltnshp_name = ?, " +
		"prgy_vlg = ?, " +
		"prgy_mdl = ?, " +
		"prgy_dst = ?, " +
		"prgy_calf_dob = ?, " +
		"prgy_breed_type = ?, " +
		"prgy_prcnt = ?, " +
		"prgy_sex = ?, " +
		"prgy_color = ?, " +
		"prgy_tagno = ?, " +
		"prgy_breed = ?, " +
		"prgy_calving_num = ?, " +
		"prgy_ai_num = ?, " +
		"prgy_prv_yield = ?, " +
		"prgy_prst_yield = ?, " +
		"prgy_bullno = ?, " +
		//"prgy_brth_wgt = ?, " +
		//"prgy_mth_wgt = ?, " +
		"prgy_dm_milk = ?, " +
		"prgy_sire_breed = ? " +
		//"prgy_nutrition = ? " +
		"WHERE prgy_id = ?";

	public static final String DELETE_PROGENCY = 
		"DELETE FROM progency " +
		"WHERE prgy_id = ?";

	// Procurementsemen

	public static final String ADD_PRSEMEN = 
		"INSERT INTO pr_semen (ps_code, ps_rcpt, ps_frmStat, ps_breed, ps_bullNo, ps_noStraws, ps_dscrd) " +
		"VALUES(?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_PRSEMEN =
		"UPDATE pr_semen SET " +
		"ps_code = ?, " +
		"ps_rcpt = ?, " +
		"ps_frmStat = ?, " +
		"ps_breed = ?, " +
		"ps_bullNo = ?, "+    
		"ps_noStraws = ?, "+
		"ps_dscrd = ? "+
		"WHERE ps_id = ?";

	public static final String DELETE_PRSEMEN =
		"DELETE FROM pr_semen " +
		"WHERE ps_id = ?";

	public static final String SELECT_PRSEMEN = 
		"SELECT ps_id, ps_code, ps_rcpt, ps_frmStat, ps_breed, ps_bullNo, ps_noStraws, ps_dscrd " +
		"FROM pr_semen " +
		"WHERE ps_id BETWEEN ? AND ? " +
		"AND ps_code LIKE ? " +
		"AND ps_breed LIKE ? " +
		"AND ps_bullNo LIKE ? " +
		"AND ps_frmStat LIKE ?";

	//ProcurementLn

	public static final String ADD_LN = 
		"INSERT INTO pr_ln (pln_code, pln_rcpt, pln_kgs, pln_cnvrtLtrs, pln_frmStat, pln_vhclNo, pln_evopLoss) " +
		"VALUES(?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_LN = 
		"UPDATE pr_ln SET " +
		"pln_code = ?, " +
		"pln_rcpt = ?, " +
		"pln_kgs = ?, " +
		"pln_cnvrtLtrs = ?, " +
		"pln_frmStat = ?, " +
		"pln_vhclNo = ?, " +
		"pln_evopLoss = ? " +
		"WHERE pln_id = ?";

	public static final String DELETE_LN = 
		"DELETE FROM pr_ln " +
		"WHERE pln_id = ?"; 

	public static final String SELECT_LN =
		"SELECT pln_id, pln_code, pln_rcpt, pln_kgs, pln_cnvrtLtrs, pln_frmStat, pln_vhclNo, pln_evopLoss " +
		"FROM pr_ln " +
		"WHERE pln_id BETWEEN ? AND ? " +
		"AND pln_code LIKE ? " +
		//"AND pln_rcpt LIKE ? " +
		"AND pln_frmStat LIKE ? " +
		"AND pln_vhclNo LIKE ?";

	//DistributionSemen

	public static final String ADD_DISSEMEN = 
		"INSERT INTO dis_semen (ds_code, ds_dt_dstrbtn, ds_breed, ds_bulNo, ds_no_straws, ds_value, ds_dcrd) " +
		"VALUES(?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_DISSEMEN = 
		"UPDATE dis_semen SET " +
		"ds_code = ?, " +
		"ds_dt_dstrbtn = ?, " +
		"ds_breed = ?, " +
		"ds_bulNo = ?, " +
		"ds_no_straws = ?, " +
		"ds_value = ?, " +
		"ds_dcrd = ? " +
		"WHERE ds_id = ?";     

	public static final String DELETE_DISSEMEN = 
		"DELETE FROM dis_semen " +
		"WHERE ds_id = ?";

	public static final String SELECT_DISSEMEN = 
		"SELECT ds_id, ds_code, ds_dt_dstrbtn, ds_breed, ds_bulNo, ds_no_straws, ds_value, ds_dcrd " +
		"FROM dis_semen " +
		"WHERE ds_id BETWEEN ? AND ? " +
		"AND ds_code LIKE ? " +
		"AND ds_breed LIKE ? " +
		"AND ds_bulNo LIKE ?";

	//DistributionLn

	public static final String ADD_DISLN = 
		"INSERT INTO dis_ln(dln_code, dln_dt_dstrbtn, dln_kgs, dln_cnvtrdLtrs, dln_frmstat, dln_vehno, dln_evoploss) " +
		"VALUES(?, ?, ?, ?, ?, ?, ?)";

	public static final String EDIT_DISLN = 
		"UPDATE dis_ln SET " +
		"dln_code = ?, " +
		"dln_dt_dstrbtn = ?, " +
		"dln_kgs = ?, " +
		"dln_cnvtrdLtrs = ?, " +
		"dln_frmstat = ?, " +
		"dln_vehno = ?, " +
		"dln_evoploss = ? " +
		"WHERE dln_id = ?";

	public static final String DELETE_DISLN =
		"DELETE FROM dis_ln " +
		"WHERE dln_id = ?";

	public static final String SELECT_DISLN = 
		"SELECT dln_id, dln_code, dln_dt_dstrbtn, dln_kgs, dln_cnvtrdLtrs, dln_frmstat, dln_vehno, dln_evoploss " +
		"FROM dis_ln " +
		"WHERE dln_id BETWEEN ? AND ? " +
		"AND dln_code LIKE ? " +
		"AND dln_frmstat LIKE ? " +
		"AND dln_vehno LIKE ?";

	//ProgencyFollowUp
	public static final String ADD_PRGYFOLLWUP = 
		"INSERT INTO prgy_followup (prgy_flw_deworm_date, prgy_flw_vacc_date, prgy_flw_detick_date, " +
		"prgy_flw_othr, prgy_flw_nutrition, prgy_flw_brth_wgt, prgy_flw_mth_wgt, prgy_flw_prgyid) " +
		"VALUES(?, ?, ?, ?, ?, " +
		"?, ?, ?)";

	public static final String EDIT_PRGYFOLLWUP = 
		"UPDATE prgy_followup SET " + 
		"prgy_flw_deworm_date = ?, " +
		"prgy_flw_vacc_date = ?, " +
		"prgy_flw_detick_date = ?, " +
		"prgy_flw_othr = ?, " +
		"prgy_flw_nutrition = ?, " +
		"prgy_flw_brth_wgt = ?, " +
		"prgy_flw_mth_wgt = ?, " +
		"prgy_flw_prgyid = ? " +
		"WHERE prgy_flw_id = ?";     

	public static final String DELETE_PRGYFOLLWUP = 
		"DELETE FROM prgy_followup " +
		"WHERE prgy_flw_id = ?";

	public static final String DELETE_PRGYFOLLWUP_ON_PRGYID = 
		"DELETE FROM prgy_followup " +
		"WHERE prgy_flw_prgyid = ?";


	public static final String SELECT_PRGYFOLLWUP = 
		"SELECT prgy_flw_id, prgy_flw_deworm_date, prgy_flw_vacc_date, prgy_flw_detick_date, " +
		"prgy_flw_brth_wgt, prgy_flw_mth_wgt, prgy_flw_othr, prgy_flw_nutrition, prgy_flw_prgyid " +
		"FROM prgy_followup " +
		"WHERE prgy_flw_id BETWEEN ? AND ? " +
		"AND prgy_flw_prgyid BETWEEN ? AND ? " +
		"AND prgy_flw_action = ?"; 

	public static final String SELECT_PRGYFOLLWUP_ON_PRGYID = 
		"SELECT prgy_flw_id, prgy_flw_deworm_date, prgy_flw_vacc_date, prgy_flw_detick_date, " +
		"prgy_flw_brth_wgt, prgy_flw_mth_wgt, prgy_flw_othr, prgy_flw_nutrition, prgy_flw_prgyid " +
		"FROM prgy_followup " +
		"WHERE prgy_flw_prgyid = ?"; 



}//end class
